# import time
# from multiprocessing import Process, Queue
# import multiprocessing
# from threading import Thread
from binascii import unhexlify

import pymysql
import datetime


# import modbus_tk.defines as cst
# import modbus_tk.modbus_tcp as modbus_tcp

# from math import ceil
from numpy import average

# import os
# from openpyxl import load_workbook

# from configparser import ConfigParser
# import logging
# from logging import handlers

import json


stasFineAS = [
    "1006R",
    "1008B",
    "1010M",
    "1020A",
    "1030A",
    "1035B",
    "1050M",
    "1055M",
    "1060M",
    "1070M",
    "1080A",
    "1090M",
    "1100M",
    "1110A",
    "1115B",
    "1120A",
    "1126R",
    "1140M",
    "1150M",
    "1160A",
    "1170M",
    "1180M",
    "1190A",
    "1196R",
    "1210S1",
    "1210S3",
    "1220M",
    "1230M",
    "1240A",
    "1250M",
    "1260M",
    "1261M",
    "1265R",
    "1270M",
    "1275A",
    "1280M",
    "1290A",
    "1300M",
    "1310A",
    "1315B",
    "1320A",
    "1330M",
    "1340M",
    "1350A-1",
    "1350A-2",
    "1355B",
    "1360M",
    "1370A",
    "1380M",
    "1390M",
    "1400A-1",
    "1400A-2",
    "1405B",
    "1410M",
    "1416R",
    "1420M",
    "1430M-1",
    "1445R",
    "2006R",
    "2010M",
    "2010M",
    "2020M",
    "2030M",
    "2050A-1",
    "2050A-2",
    "2055B",
    "2060M",
    "2070M",
    "2080M",
    "2090M",
    "2110M",
    "2120M",
    "2130A-1",
    "2130A-2",
    "2140M",
    "2145R",
    "2160A",
    "2170M",
    "2180A",
    "2185B",
    "2190M",
    "2225R",
    "2230M",
    "2240M",
    "2250M",
    "2280M",
    "2290M",
    "2300M",
    "2310M",
    "2320M",
    "2340M",
    "2350A",
    "2356R",
    "2360M",
    "2370M",
    "2390M",
    "2400M",
    "2410M",
    "2420M",
    "2430M",
    "2440M",
    "2460M",
    "2470M",
    "2475A",
    "2480M",
    "2490M",
    "3010M",
    "3020M",
    "3030S1",
    "3030S2",
    "3040S",
    "3050M",
    "3060A1",
    "3070A",
    "3080A",
    "3090A",
    "3100A",
    "3105R",
    "3110A",
    "3120M",
]


def getAsData(station, cycleStandard):
    # 初始化变量
    # 新增2个字段
    # overcycle_positive_three = 0
    # overcycle_negative_three = 0

    # 获取当前时间
    current_time = datetime.datetime.now()
    current_time_string = current_time.strftime("%Y-%m-%d %H:%M:%S")
    # 计算当前日期8点的时间
    eight_in_the_morning = current_time.replace(hour=8, minute=0, second=0, microsecond=0)

    # 如果当前时间已超过8点，则取当天的8点，否则取昨天的8点
    if current_time.time() < eight_in_the_morning.time():
        eight_in_the_morning -= datetime.timedelta(days=1)
    # 转换为字符串
    eight_in_the_morning_string = eight_in_the_morning.strftime("%Y-%m-%d %H:%M:%S")

    # 输出当前时间
    print(current_time_string)
    print(eight_in_the_morning_string)

    good = 0
    reject = 0

    if "R" in station:
        limitNo = 1
    else:
        limitNo = 100
    try:
        cnx = pymysql.connect(host="10.64.35.32", port=3306, user="root", password="12345678", db="assembly")
        cur = cnx.cursor()
        # WHERE order_date BETWEEN '2022-01-01 00:00:00' AND '2022-01-31 23:59:59';
        sqlfun = (
            "select * from assembly.output_loopa where station = '%s' and time between '%s' AND '%s' order by id desc"
            % (station, eight_in_the_morning_string, current_time_string)
        )
        # sqlfun = "select * from assembly.output_loopa where station = '%s' order by id desc limit %d" % (
        #     station,
        #     limitNo,
        # )
        cur.execute(sqlfun)
        data = cur.fetchall()
        cnx.commit()
        cur.close()
        cnx.close()
        cycletmp = [x[4] for x in data]

        print("cycletmp数据如下:")
        print(cycletmp)

        ##        a = cycletmp.count(0)
        ##
        ##        for i in range(a):
        ##            cycletmp.remove(0)
        # 对cycletmp进行倒序排序
        cycletmp = cycletmp[::-1]
        print("cycletmp倒序数据如下:")
        print(cycletmp)
        # cycletmp为output_loopa中last_cycle_time字段的值组成的列表

        # 这是一个 Python 中的列表生成式，它生成了一个从 n-1 到 -1，每次减少1的整数列表。
        # 也就是说，如果n的值为5，则生成的列表为[4，3，2，1，0]

        # for i in range(len(cycletmp) - 1, -1, -1):
        #     if cycletmp[i] <= 200:
        #         del cycletmp[i]

        for i in range(len(cycletmp) - 1, -1, -1):
            if cycletmp[i] <= 200 or cycletmp[i] >= 900:
                del cycletmp[i]

        print("cycletmp去除小于200数据后:")
        print(cycletmp)

        if station in ["1430M-1"]:
            for i in range(len(cycletmp) - 1, 0, -1):
                if cycletmp[i] - cycletmp[i - 1] >= 300:
                    # print(data[i])
                    cycletmp[i] = cycletmp[i] - cycletmp[i - 1]

        if len(cycletmp) == 0:
            cycle = 0
            maxcycle = 0
            mincycle = 0
            overcycle = 0
            # 新增两个字段 author:davidhu
            overcycle_positive_three = 0
            overcycle_negative_three = 0

        else:
            cycle = average(cycletmp) / 10
            maxcycle = max(cycletmp) / 10
            mincycle = min(cycletmp) / 10
            overcycle = 0
            # 新增2个字段
            overcycle_positive_three = 0
            overcycle_negative_three = 0
            for xx in cycletmp:
                if xx > cycleStandard:
                    overcycle += 1
            # 新增两个字段的判断 author:davidhu

            print(cycleStandard * 1.03)
            print(cycleStandard * 0.97)

            for yy in cycletmp:
                if yy > cycleStandard * 1.05:
                    overcycle_positive_three = overcycle_positive_three + 1
            for yy2 in cycletmp:
                if yy2 < cycleStandard * 0.95:
                    overcycle_negative_three += 1

        good = data[0][6]
        reject = data[0][7]
        try:
            time = data[0][8].strftime("%Y-%m-%d %H:%M:%S")
        except:
            time = 0
        lastCycle = data[0][4] / 10
        pano = data[0][3]
        eun = data[0][1][:-4]
        other = data[0][9]

        print(
            cycle,
            good,
            reject,
            time,
            lastCycle,
            pano,
            eun,
            maxcycle,
            mincycle,
            overcycle,
            # 增加两个
            overcycle_positive_three,
            overcycle_negative_three,
            other,
        )

        return (
            cycle,
            good,
            reject,
            time,
            lastCycle,
            pano,
            eun,
            maxcycle,
            mincycle,
            overcycle,
            # 增加两个
            overcycle_positive_three,
            overcycle_negative_three,
            other,
        )

    except Exception as e:
        print("出错啦！" + str(e))


def updateFineAs(station):
    keys = {"1120A": "TTT", "2050A-2": "torque", "1110A": "torque"}
    others = {
        "1010M": "5C",
        "1060M": "5C",
        "1210S3": "5C",
        "1170M": "5C",
        "1210S1": "5C",
        "2350A": "dis",
        "3060A1": "dis",
        "2470M": "leak",
        "2130A-1": "leak",
        "2130A-2": "leak",
        "3090A": "leak",
        "2340M": "clutch",
        "2170M": "flywheel",
        "2050A-2": "torque",
    }
    try:
        cycleStandards = getCycleStandard()
        try:
            if int(station[:4]) in cycleStandards:
                cycleStandard = cycleStandards[int(station[:4])] * 10
            else:
                cycleStandard = 440
        except:
            cycleStandard = 440

        (
            cycle,
            good,
            reject,
            time,
            lastCycle,
            pano,
            eun,
            maxcycle,
            mincycle,
            overcycle,
            overcycle_positive_three,
            overcycle_negative_three,
            other,
        ) = getAsData(station, cycleStandard)

        try:
            ftq = good / (good + reject)
        except:
            ftq = 0

        cnx = pymysql.connect("127.0.0.1", "root", "12345678", "assembly")
        cur = cnx.cursor()

        if station in keys:
            a = getStaKeyPara(station, eun, keys[station])
            ##            print('##########################返回数据：',a)

            if a != "":
                if station == "2050A-2" and eun[3:5] == "TB":
                    pass
                else:
                    a = json.dumps({keys[station]: a})
                    sqlfun = (
                        "UPDATE assembly.fine_as SET count_good=%d,count_reject=%d,"
                        "ftq=%f,cycletime=%f,time='%s',eun='%s',lastcycle=%f,pano=%d,"
                        "maxcycle=%f,mincycle=%f,overcycle=%d,overcycle_positive_three=%d,overcycle_negative_three=%d,keypara = '%s' WHERE station = '%s'"
                        % (
                            good,
                            reject,
                            ftq,
                            cycle,
                            time,
                            eun,
                            lastCycle,
                            pano,
                            maxcycle,
                            mincycle,
                            overcycle,
                            overcycle_positive_three,
                            overcycle_negative_three,
                            a,
                            station,
                        )
                    )
                cur.execute(sqlfun)
                print("#######################SQL executed#############################: ", sqlfun)
            else:
                sqlfun = (
                    "UPDATE assembly.fine_as SET count_good=%d,count_reject=%d,"
                    "ftq=%f,cycletime=%f,time='%s',eun='%s',lastcycle=%f,pano=%d,"
                    "maxcycle=%f,mincycle=%f,overcycle=%d,overcycle_positive_three=%d,overcycle_negative_three=%d WHERE station = '%s'"
                    % (
                        good,
                        reject,
                        ftq,
                        cycle,
                        time,
                        eun,
                        lastCycle,
                        pano,
                        maxcycle,
                        mincycle,
                        overcycle,
                        overcycle_positive_three,
                        overcycle_negative_three,
                        station,
                    )
                )

                cur.execute(sqlfun)
                print("%%%%%%%%%%%%%%%%%%%%%%%%%%%SQL executed%%%%%%%%%%%%%%%%%%%%%%: ", sqlfun)

        if station in others:
            if other not in [None, ""]:
                other = json.loads(other)
                b = {}
                for bb in other:
                    # bb = others[station] + self.keys[bb] + ':' + other[bb] + '\n'
                    b[bb] = other[bb][0]
                b = json.dumps({others[station]: b})

                sqlfun = (
                    "UPDATE assembly.fine_as SET count_good=%d,count_reject=%d,"
                    "ftq=%f,cycletime=%f,time='%s',eun='%s',lastcycle=%f,pano=%d,"
                    "maxcycle=%f,mincycle=%f,overcycle=%d,overcycle_positive_three=%d,overcycle_negative_three=%d,keypara = '%s' WHERE station = '%s'"
                    % (
                        good,
                        reject,
                        ftq,
                        cycle,
                        time,
                        eun,
                        lastCycle,
                        pano,
                        maxcycle,
                        mincycle,
                        overcycle,
                        overcycle_positive_three,
                        overcycle_negative_three,
                        b,
                        station,
                    )
                )
                cur.execute(sqlfun)
            ##                print("￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥SQL executed: ", sqlfun)
            elif station in ["1210S3", "2130A-2"]:
                if eun[3:5] not in ["RA", "CS", "DF", "RE", "RC", "RF", "SA", "SB", "TB", "BH"]:
                    sqlfun = (
                        "UPDATE assembly.fine_as SET count_good=%d,count_reject=%d,"
                        "ftq=%f,cycletime=%f,time='%s',eun='%s',lastcycle=%f,pano=%d,"
                        "maxcycle=%f,mincycle=%f,overcycle=%d,overcycle_positive_three=%d,overcycle_negative_three=%d WHERE station = '%s'"
                        % (
                            good,
                            reject,
                            ftq,
                            cycle,
                            time,
                            eun,
                            lastCycle,
                            pano,
                            maxcycle,
                            mincycle,
                            overcycle,
                            overcycle_positive_three,
                            overcycle_negative_three,
                            station,
                        )
                    )
                    cur.execute(sqlfun)
            ##                    print("￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥SQL executed: ", sqlfun)

            elif station in ["1210S1", "2130A-1"]:
                if eun[3:5] not in ["RZ", "AZ", "BW", "SM"]:
                    sqlfun = (
                        "UPDATE assembly.fine_as SET count_good=%d,count_reject=%d,"
                        "ftq=%f,cycletime=%f,time='%s',eun='%s',lastcycle=%f,pano=%d,"
                        "maxcycle=%f,mincycle=%f,overcycle=%d,overcycle_positive_three=%d,overcycle_negative_three=%d WHERE station = '%s'"
                        % (
                            good,
                            reject,
                            ftq,
                            cycle,
                            time,
                            eun,
                            lastCycle,
                            pano,
                            maxcycle,
                            mincycle,
                            overcycle,
                            overcycle_positive_three,
                            overcycle_negative_three,
                            station,
                        )
                    )
                    cur.execute(sqlfun)
            ##                    print("￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥SQL executed: ", sqlfun)

            else:
                sqlfun = (
                    "UPDATE assembly.fine_as SET count_good=%d,count_reject=%d,"
                    "ftq=%f,cycletime=%f,time='%s',eun='%s',lastcycle=%f,pano=%d,"
                    "maxcycle=%f,mincycle=%f,overcycle=%d,overcycle_positive_three=%d,overcycle_negative_three=%d WHERE station = '%s'"
                    % (
                        good,
                        reject,
                        ftq,
                        cycle,
                        time,
                        eun,
                        lastCycle,
                        pano,
                        maxcycle,
                        mincycle,
                        overcycle,
                        overcycle_positive_three,
                        overcycle_negative_three,
                        station,
                    )
                )
                cur.execute(sqlfun)
        ##                print("￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥SQL executed: ", sqlfun)

        else:
            if station == "2050A-2":
                if eun[3:5] == "TB":
                    sqlfun = (
                        "UPDATE assembly.fine_as SET count_good=%d,count_reject=%d,"
                        "ftq=%f,cycletime=%f,time='%s',eun='%s',lastcycle=%f,pano=%d,"
                        "maxcycle=%f,mincycle=%f,overcycle=%d,overcycle_positive_three=%d,overcycle_negative_three=%d WHERE station = '%s'"
                        % (
                            good,
                            reject,
                            ftq,
                            cycle,
                            time,
                            eun,
                            lastCycle,
                            pano,
                            maxcycle,
                            mincycle,
                            overcycle,
                            overcycle_positive_three,
                            overcycle_negative_three,
                            station,
                        )
                    )
                    cur.execute(sqlfun)
            ##                    print("￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥SQL executed: ", sqlfun)

            else:
                sqlfun = (
                    "UPDATE assembly.fine_as SET count_good=%d,count_reject=%d,"
                    "ftq=%f,cycletime=%f,time='%s',eun='%s',lastcycle=%f,pano=%d,"
                    "maxcycle=%f,mincycle=%f,overcycle=%d,overcycle_positive_three=%d,overcycle_negative_three=%d WHERE station = '%s'"
                    % (
                        good,
                        reject,
                        ftq,
                        cycle,
                        time,
                        eun,
                        lastCycle,
                        pano,
                        maxcycle,
                        mincycle,
                        overcycle,
                        overcycle_positive_three,
                        overcycle_negative_three,
                        station,
                    )
                )
                cur.execute(sqlfun)
        ##                print("￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥SQL executed: ", sqlfun)

        cnx.commit()
        cur.close()
        cnx.close()
    except Exception as e:
        print(e)


getAsData("1260M", 400)
